﻿use persons
go
------------------8----------------------------------------------------------------
/*Find parent with the biggest amount of contacts*/
SELECT TOP 1 * 
FROM (SELECT 
COUNT([Person].[ID]) AS [AMOUNT], 
[Person].[ID],
[Person].[FirstName],
[Person].[LastName] 
FROM [dbo].[Person], [dbo].[Contact]
WHERE [Person].[ID]=[Contact].[fk_personId] AND [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [FATHER]
WHERE [Person].[ID]=[FATHER].[fk_fatherId])
GROUP BY [Person].[ID], [Person].[FirstName], [Person].[LastName]
UNION
SELECT 
COUNT([Person].[ID])AS [AMOUNT], 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person], [dbo].[Contact]
WHERE [Person].[ID]=[Contact].[fk_personId] AND [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [MOTHER] WHERE [Person].[ID]=[MOTHER].[fk_motherId])
GROUP BY [Person].[ID],[Person].[FirstName], [Person].[LastName]) AS [P]
ORDER BY [AMOUNT] DESC

SELECT TOP 1 * 
FROM (SELECT 
COUNT([Person].[ID]) AS [AMOUNT], 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person] INNER JOIN [dbo].[Contact]
ON [Person].[ID]=[Contact].[fk_personId] AND [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [FATHER] 
ON [Person].[ID]=[FATHER].[fk_fatherId])
GROUP BY [Person].[ID], [Person].[FirstName], [Person].[LastName]
UNION
SELECT 
COUNT([Person].[ID])AS [AMOUNT], 
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person] INNER JOIN [dbo].[Contact]
ON [Person].[ID]=[Contact].[fk_personId] AND [Contact].[fk_personId] IN(SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [MOTHER] 
ON [Person].[ID]=[MOTHER].[fk_motherId])
GROUP BY [Person].[ID], [Person].[FirstName], [Person].[LastName] ) AS [P]
ORDER BY [AMOUNT] DESC

SELECT TOP 1 
[CONT].[AMOUNT] [MAXAMOUNT],
[Person].[ID],
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person] INNER JOIN (SELECT 
[Contact].[fk_personId], 
COUNT(*) [AMOUNT] 
FROM [dbo].[Contact] 
GROUP BY [fk_personId]) [CONT]
ON ([Person].[ID] = [CONT].[fk_personId])
WHERE [ID] IN (SELECT DISTINCT  
[MOM].[ID] 
FROM [dbo].[Person] AS [MOM], [dbo].[Person] AS [DAD]
WHERE (([MOM].[ID] = [DAD].[fk_motherId]) OR ([MOM].[ID] = [DAD].[fk_fatherId])))
ORDER BY [MAXAMOUNT] DESC
